package org.jeecg.chase.common.dataaccess.core;

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import org.jeecg.chase.common.dataaccess.data.PageRequest;
import org.jeecg.chase.common.dataaccess.data.PageResultSetExtractor;
import org.jeecg.chase.common.dataaccess.supper.DatabaseSupport;
import org.jeecg.chase.common.dataaccess.supper.HumpColumnRowMapper;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.StringReader;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author leikai
 * @version 1.0
 * @description: 基于Jdbc(占位符)实现的数据库操作底层实现类
 * @date 2021/07/23/0023 15:36
 */
@Component
public class BaseNamedParameterJdbcTemplate {

    @Resource(name = "namedParameterJdbcTemplate")
    private NamedParameterJdbcTemplate jdbcTemplate;


    public NamedParameterJdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    /**
     * 执行sql脚本
     *
     * @param sql
     * @return void
     * @author leikai
     * @date 2021/07/23/0023 15:38
     */
    @SuppressWarnings("unchecked")
    public void execute(final String sql) {
        jdbcTemplate.execute(sql, new PreparedStatementCallback() {
            @Override
            public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                ps.execute();
                return null;
            }
        });
    }

    /**
     * 分页查询
     *
     * @param pageRequest
     * @param sql
     * @param paramObject
     * @param elementType
     * @return com.smallchill.common.dataaccess.data.PageResultSetExtractor<?>
     * @author leikai
     * @date 2021/07/01/0001 18:18
     */
    @SuppressWarnings("unchecked")
    public PageResultSetExtractor<?> queryForPage(PageRequest pageRequest, String sql, Object paramObject, Class<?> elementType) {
        //1.进行预处理
        Map<String, Object> preResultMap = preQueryForPage(pageRequest, sql, paramObject);
        //2.定义返回结果，设置总记录数
        PageResultSetExtractor pageResult = new PageResultSetExtractor(null, (Long) preResultMap.get("total"));
        //3.进行数据库查询
        List<?> list = jdbcTemplate.query((String) preResultMap.get("sql"), sqlParameterSource(paramObject), DatabaseSupport.rowMapper(elementType));
        //4.返回
        pageResult.setResult(list);
        return pageResult;
    }

    /**
     * 列表查询
     *
     * @param sql
     * @param paramObject
     * @param elementType
     * @return java.util.List
     * @author leikai
     * @date 2021/07/05/0005 9:18
     */
    public List queryForList(String sql, Object paramObject, Class elementType) {
        //1.设置参数类型
        SqlParameterSource sqlParameterSource = sqlParameterSource(paramObject);
        RowMapper rowMapper = DatabaseSupport.rowMapper(elementType);
        //2.查询列表
        List list = jdbcTemplate.query(sql, sqlParameterSource, rowMapper);
        return list;
    }

    /**
     * 分页查询（驼峰）
     *
     * @param pageRequest
     * @param sql
     * @param paramObject
     * @return com.smallchill.common.dataaccess.data.PageResultSetExtractor<?>
     * @author leikai
     * @date 2021/07/01/0001 18:19
     */
    public PageResultSetExtractor<?> queryForPageHump(PageRequest pageRequest, String sql, Object paramObject) {
        //1.进行预处理
        Map<String, Object> preResultMap = preQueryForPage(pageRequest, sql, paramObject);
        //2.定义返回结果，设置总记录数
        PageResultSetExtractor pageResult = new PageResultSetExtractor(null, (Long) preResultMap.get("total"));
        //3.进行数据库查询
        List<Map<String, Object>> list = jdbcTemplate.query((String) preResultMap.get("sql"), sqlParameterSource(paramObject), new HumpColumnRowMapper());
        //4.返回
        pageResult.setResult(list);
        return pageResult;
    }


    private Map<String, Object> preQueryForPage(PageRequest pageRequest, String sql, Object paramObject) {
        //1.验证参数是否有效
        if (pageRequest == null) {
            new IllegalArgumentException("pageRequest不能为空");
        }
        Map<String, Object> preResultMap = new HashMap<String, Object>();
        //2.获取总记录数
        if (pageRequest.isCountTotal()) {
            StringBuilder totalRowsSql = new StringBuilder();
            totalRowsSql.append(sql).insert(0, " select count(*) totalRows from ( ").append(" ) totalRowsSql ");
            Long total = this.queryForLong(totalRowsSql.toString(), paramObject);
            preResultMap.put("total", total);
        } else {
            preResultMap.put("total", 0L);
        }
        //3.设置排序子句
        if (pageRequest.isOrderBySetted()) {
            sql = setOrderParameterToSql(sql, pageRequest);
        }
        //4.根据不同数据库设置数据库分页sql
        String pageSql = getPageSql(pageRequest, sql);
        preResultMap.put("sql", pageSql);
        //5.返回预处理结果
        return preResultMap;
    }

    public List<Map<String, Object>> queryForListHump(String sql, Object paramObject) {
        //1.设置参数类型
        SqlParameterSource sqlParameterSource = sqlParameterSource(paramObject);
        //2.查询列表
        List<Map<String, Object>> list = jdbcTemplate.query(sql, sqlParameterSource, new HumpColumnRowMapper());
        return list;
    }

    /**
     * 获取分页sql
     *
     * @param pageRequest
     * @param sqlStr
     * @return java.lang.String
     * @author leikai
     * @date 2021/07/01/0001 18:18
     */
    public String getPageSql(final PageRequest pageRequest, String sqlStr) {
        int start = (pageRequest.getPageNo() - 1) * pageRequest.getPageSize();
        int end = start + pageRequest.getPageSize();
        String database = DatabaseSupport.getDatabaseType(((JdbcTemplate) jdbcTemplate.getJdbcOperations()).getDataSource());
        if ("mysql".equals(database)) {
            return sqlStr + " limit " + start + "," + pageRequest.getPageSize();
        }
        if ("oracle".equals(database)) {
            String sql = null;
            try {
                CCJSqlParserManager parserManager = new CCJSqlParserManager();
                Select select = (Select) parserManager.parse(new StringReader(sqlStr));
                PlainSelect plain = (PlainSelect) select.getSelectBody();
                List<OrderByElement> OrderByElements = plain.getOrderByElements();
                List<OrderByElement> newOrderByElements = new ArrayList<OrderByElement>();
                for (OrderByElement orderByElement : OrderByElements) {
                    String orderbyStr = orderByElement.toString();
                    OrderByElement elemnt = new OrderByElement();
                    Expression expression = new net.sf.jsqlparser.schema.Column(orderbyStr);
                    elemnt.setExpression(expression);
                    newOrderByElements.add(elemnt);
                }
                OrderByElement elemnt = new OrderByElement();
                Expression expression = new net.sf.jsqlparser.schema.Column("rownum");
                elemnt.setExpression(expression);
                newOrderByElements.add(elemnt);
                plain.setOrderByElements(newOrderByElements);
                sqlStr = plain.toString();
            } catch (Exception e) {
            }
            sql = "select * from ( select row_.*, rownum rownum_ from ( " + sqlStr + ") row_ where rownum <=" + end + " ) where rownum_ > " + start;
            return sql;
        }
        if ("postgresql".equals(database)) {
            return sqlStr + " limit " + pageRequest.getPageSize() + " offset " + start;
        }
        if ("sqlserver".equalsIgnoreCase(database)) {
            return "select top " + end + sqlStr.substring(sqlStr.toLowerCase().indexOf("select") + 6);
        }
        return "";
    }

    /**
     * 返回长整型
     *
     * @param sql
     * @param paramObject
     * @return long
     * @author leikai
     * @date 2021/07/01/0001 18:16
     */
    public long queryForLong(String sql, Object paramObject) {
        //1.设置参数类型
        SqlParameterSource sqlParameterSource = sqlParameterSource(paramObject);
        //2.查询
        Long number = jdbcTemplate.queryForObject(sql, sqlParameterSource, Long.class);
        return (number != null ? number.intValue() : 0);
    }

    /**
     * 根据传入的参数对象获取sql参数源
     *
     * @param paramObject
     * @return org.springframework.jdbc.core.namedparam.SqlParameterSource
     * @author leikai
     * @date 2021/07/01/0001 18:16
     */
    private SqlParameterSource sqlParameterSource(Object paramObject) {
        SqlParameterSource paramSource = null;
        //默认参数变量类型为Map
        if (paramObject == null) {
            paramObject = new HashMap<String, Object>();
        }
        //默认参数变量类型为Map
        if (paramObject instanceof Map) {
            paramSource = new MapSqlParameterSource((Map) paramObject);
        } else {
            paramSource = new BeanPropertySqlParameterSource(paramObject);
        }
        return paramSource;
    }

    /**
     * 在SQL的后面添加分页参数定义的orderBy
     *
     * @param sql
     * @param pageRequest
     * @return java.lang.String
     * @author leikai
     * @date 2021/07/01/0001 18:17
     */
    protected String setOrderParameterToSql(String sql, PageRequest pageRequest) {
        try {
            CCJSqlParserManager parserManager = new CCJSqlParserManager();
            Select select = (Select) parserManager.parse(new StringReader(sql));
            PlainSelect plain = (PlainSelect) select.getSelectBody();
            List<OrderByElement> OrderByElements = plain.getOrderByElements();
            if (OrderByElements != null) {
                List<OrderByElement> newOrderByElements = new ArrayList<OrderByElement>();
                OrderByElement element = new OrderByElement();
                Expression expression = new net.sf.jsqlparser.schema.Column(pageRequest.getOrderBy());
                element.setExpression(expression);
                newOrderByElements.add(element);
                plain.setOrderByElements(newOrderByElements);
                return plain.toString();
            } else {
                StringBuilder builder = new StringBuilder(sql);
                builder.append(" order by ");
                builder.append(pageRequest.getOrderBy());
                return builder.toString();
            }
        } catch (Exception e) {
            e.printStackTrace();
            return sql;
        }
    }

    public int[] batchUpdate(String sql, List<?> batchArgs) {
        SqlParameterSource[] sqlParameterSourceArgs = new SqlParameterSource[batchArgs.size()];
        int i = 0;
        for (Object values : batchArgs) {
            sqlParameterSourceArgs[i] = sqlParameterSource(values);
            i++;
        }
        int[] result = jdbcTemplate.batchUpdate(sql, sqlParameterSourceArgs);
        return result;
    }

    public int queryForInt(String sql, Object paramObject) {
        //1.设置参数类型
        SqlParameterSource sqlParameterSource = sqlParameterSource(paramObject);
        //2.查询
        Integer number = jdbcTemplate.queryForObject(sql, sqlParameterSource, Integer.class);
        return (number != null ? number.intValue() : 0);
    }

    public void batchExecute(String[] sqls, Map<String, Object> paramMap) {
        for (String sql : sqls) {
            jdbcTemplate.execute(sql, paramMap, new PreparedStatementCallback<Integer>() {
                @Override
                public Integer doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
                    pstmt.execute();
                    return 0;
                }
            });
        }
    }

    public Map<String, Object> queryForMapHump(String sql, Object paramObject) {
        //1.设置参数类型
        SqlParameterSource sqlParameterSource = sqlParameterSource(paramObject);
        //2.查询
        try {
            Map<String, Object> result = (Map<String, Object>) jdbcTemplate.queryForObject(sql, sqlParameterSource, new HumpColumnRowMapper());
            return result;
        } catch (Exception e) {
            return null;
        }
    }

    public Map<String, Object> queryForMap(String sql, Object paramObject) {
        //1.设置参数类型
        SqlParameterSource sqlParameterSource = sqlParameterSource(paramObject);
        //2.查询
        try {
            Map<String, Object> result = jdbcTemplate.queryForMap(sql, sqlParameterSource);
            return result;
        } catch (Exception e) {
            return null;
        }
    }
}
